﻿using System;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for AddressDAL
/// </summary>
public class AddressDAL
	{
	dbConnection db = new dbConnection();
	public AddressDAL()
		{
		//
		// TODO: Add constructor logic here
		//

		}

	public int CreateAddress(AddressBO Address)
		{
		SqlConnection cn = null;
		SqlCommand cmd = null;
		int Counter = 0;


		try
			{
			cn = new SqlConnection();
			cn.ConnectionString = db.GetCS();

			cmd = new SqlCommand();
			cmd.Connection = cn;
			cmd.CommandType = CommandType.Text;
			cmd.CommandText = "INSERT INTO [Addresses] ( AddressLine1, AddressLine2,AddressLine3,City,PinCode,PhoneNumber,EmailId,AddCrtdDt) VALUES ( @AddressLine1, @AddressLine2,@AddressLine3,@City,@PinCode,@PhoneNumber,@EmailId,@AddCrtdDt)";

			cmd.Parameters.AddWithValue("@AddressLine1", Address.AddressLine1);
			cmd.Parameters.AddWithValue("@AddressLine2", Address.AddressLine2);
			cmd.Parameters.AddWithValue("@AddressLine3", Address.AddressLine3);
			cmd.Parameters.AddWithValue("@City", Address.City);
			cmd.Parameters.AddWithValue("@PinCode", Address.PinCode);
			cmd.Parameters.AddWithValue("@PhoneNumber", Address.PhoneNumber);
			cmd.Parameters.AddWithValue("@AddCrtdDt", Address.AddCrtdDt);
			cmd.Parameters.AddWithValue("@EmailId", Address.EmailId);

			cn.Open();
			Counter = cmd.ExecuteNonQuery();

			}
		catch (Exception ex)
			{
			throw new Exception(ex.Message);
			}
		finally
			{
			if (cn != null)
				{
				cn.Close();
				cn.Dispose();
				cn = null;
				}
			}
		return (Counter);
		}
	public int UpdateAddress(AddressBO Address)
		{


		SqlConnection cn = null;
		SqlCommand cmd = null;
		int Counter = 0;


		try
			{
			cn = new SqlConnection();
			cn.ConnectionString = db.GetCS();

			cmd = new SqlCommand();
			cmd.Connection = cn;
			cmd.CommandType = CommandType.Text;
			cmd.CommandText = "UPDATE [Addresses] SET  [AddressLine1]=@AddressLine1, [AddressLine2]=@AddressLine2,[AddressLine3]=@AddressLine3,[City]=@City,[PinCode]=@PinCode,[PhoneNumber]=@PhoneNumber,[AddCrtdDt]=@AddCrtdDt WHERE AddressId=@AddressId";

			cmd.Parameters.AddWithValue("@AddressId", Address.AddressId);
			cmd.Parameters.AddWithValue("@AddressLine1", Address.AddressLine1);
			cmd.Parameters.AddWithValue("@AddressLine2", Address.AddressLine2);
			cmd.Parameters.AddWithValue("@AddressLine3", Address.AddressLine3);
			cmd.Parameters.AddWithValue("@City", Address.City);
			cmd.Parameters.AddWithValue("@PinCode", Address.PinCode);
			cmd.Parameters.AddWithValue("@PhoneNumber", Address.PhoneNumber);
			cmd.Parameters.AddWithValue("@AddCrtdDt", Address.AddCrtdDt);
			cn.Open();
			Counter = cmd.ExecuteNonQuery();

			}

		
		catch (Exception ex)
			{
			throw new Exception(ex.Message);
			}
		finally
			{
			if (cn != null)
				{
				cn.Close();
				cn.Dispose();
				cn = null;
				}
			}
		return (Counter);
		}
	public int DeleteAddress(AddressBO Address)
		{
		SqlConnection cn = null;
		SqlCommand cmd = null;
		int Counter = 0;


		try
			{
			cn = new SqlConnection();
			cn.ConnectionString = db.GetCS();

			cmd = new SqlCommand();
			cmd.Connection = cn;
			cmd.CommandType = CommandType.Text;
			cmd.CommandText = "DELETE FROM [Addresses] WHERE AddressId=@AddressId";
			cmd.Parameters.AddWithValue("@AddressId", Address.AddressId);

			cn.Open();
			Counter = cmd.ExecuteNonQuery();

			}
		catch (Exception ex)
			{
			throw new Exception(ex.Message);
			}
		finally
			{
			if (cn != null)
				{
				cn.Close();
				cn.Dispose();
				cn = null;
				}
			}
		return (Counter);
		}
	public DataTable Load()
		{

		SqlConnection cn = null;
		SqlCommand cmd = null;
		DataSet dSet = null;
		SqlDataAdapter sda = null;

		try
			{
			cn = new SqlConnection();
			cn.ConnectionString = db.GetCS();
			cmd = new SqlCommand();
			cmd.Connection = cn;
			cmd.CommandType = CommandType.Text;
			//cmd.CommandText = "Select AddressId,AddressLine1+','+AddressLine2 + ',' +AddressLine3+','+City+','+CAST( PinCode AS nvarchar(50))+','+CAST( PhoneNumber AS nvarchar(50)) as Address,AddCrtdDt from [Addresses]";
			cmd.CommandText = "Select AddressId,AddressLine1,AddressLine2,AddressLine3,City,PinCode, PhoneNumber,AddCrtdDt from [Addresses]";

			dSet = new DataSet();

			sda = new SqlDataAdapter(cmd);

			sda.Fill(dSet, "Addresses");

			return dSet.Tables["Addresses"];

			}

		catch
			{

			throw;

			}

		finally
			{

			dSet.Dispose();

			sda.Dispose();

			cn.Close();

			cn.Dispose();

			}
		}
	}